import csv
import time

import pandas as pd
from django.http import HttpResponse,JsonResponse
from lxml import etree
from selenium.webdriver import Chrome           # 引入selenium浏览器自动框架、选择谷歌浏览器chrome类
from selenium.webdriver.chrome.service import Service       # 防止被反扒，
from selenium.webdriver.chrome.options import Options           # 隐藏浏览器痕迹的参数
from selenium.webdriver.common.by import By         # 定位标签by
from selenium.webdriver.common.action_chains import ActionChains            # 动作链条，拖动，下拉什么的
import json
from pprint import pprint
from random import randint
from lxml import etree
import pymysql
from db.models import *


def data_chong(request):


    # 爬虫运行
    # alist = diff_page()
    # alist[0].to_csv('11.csv')
    # alist[1].to_csv('22.csv')
    # alist[2].to_csv('33.csv')
    # alist[3].to_csv('44.csv')

    df = pd.read_csv('11.csv')
    df2 = pd.read_csv('22.csv')
    df3 = pd.read_csv('33.csv')
    df4 = pd.read_csv('44.csv')
    alist = [df, df2, df3, df4]



    for d in alist[0].itertuples():
        economy.objects.create(year=d.year,gdp=d.gdp, per_income=d.per_income, cpi=d.cpi, eva=d.eva, city='chongqing')
    # year = year-1
    for d in alist[1].itertuples():
        ecology.objects.create(year=d.year,forest_cover=d.forest_cover, forest_area=d.forest_area, water_resource=d.water_resource, dirty_water=d.dirty_water, city='chongqing')
    # year = year - 1
    for d in alist[2].itertuples():
        carbon.objects.create(year=d.year,raw_coal=d.raw_coal, natural_gas=d.natural_gas, oil=d.oil, carbon_dioxide=d.carbon_dioxide, city='chongqing')
    # year = year - 1
    for d in alist[3].itertuples():
        energy.objects.create(year=d.year,energy_c=d.energy_c, coal_c=d.coal_c, gas_c=d.gas_c, oil_c=d.oil_c,electricity_c = d.electricity_c,city='chongqing')
    # year = year - 1
    res = 0
    return JsonResponse({'message': res})

def diff_page():
    js = 'window.scrollTo(0, document.documentElement.scrollHeight)'  # js注入的脚本命令，该动作为，鼠标滑倒网页的最底部....

    service = Service()
    opt = Options()
    opt.add_argument('--disable-blink-features=AutomationControlled')  # 类似于headers操作.  隐藏浏览器请求头文件
    opt.add_experimental_option('detach', True)  # 防止报错，浏览器自动关闭
    # opt.headless = True  # 开启无头模式，减少io对内存的占用，提升速度     不用打开浏览器就能获取数据

    url = 'https://data.tjj.cq.gov.cn/cqtjjfbk/#/publishingIndicatorSearch/ndsj?themeCode=sjndsjFbk&folderId=ba1feebb06d645e084007359ce882c23'
    driver = Chrome(service=service, options=opt)  # 实例化chrome.   添加参数
    driver.maximize_window()  # z最大化窗口
    driver.get(url)  # 自动化，打开网页
    time.sleep(6)  # 等待3秒，让浏览器加载数据，渲染数据...
    #driver.execute_script(js)
    time.sleep(1)
    print('111')
    time.sleep(1)
    page_text1 = driver.page_source
    #print(page_text1)
    tree = etree.HTML(page_text1)
    data1 = tree.xpath('//tr[@class="el-table__row"]/td')
    title1 = data1[0].xpath('./div/span/text()')[0]
    print(title1)

    ###### 五年的数据....#######


    #################  part1.经济建设    #################
    eco_gdp = []
    #### 1、gdp生产总值------------------------------------------------------------------------------------------------
    for info in data1[1:6]:
        num = info.xpath('./div/div/span/text()')[0]
        print(num)
        eco_gdp.append(num)
    time.sleep(1)

    eco_add_fact = []
    # 4、工业增加值....       在同一个页面中...
    data3 = tree.xpath('//tr[@class="el-table__row"]')[2]
    data3 = data3.xpath('./td')
    title3 = data3[0].xpath('./div/span/text()')[0]
    print(title3)
    #### 4、工业增加值---------------------------------------------------------------------------------------------------
    for info in data3[1:]:
        num = info.xpath('./div/div/span/text()')[0]
        print(num,'add')
        eco_add_fact.append(num)
    # driver.close()
    # driver.quit()




    # 2、人均可支配收入.
    url1 = 'https://data.tjj.cq.gov.cn/cqtjjfbk/#/publishingIndicatorSearch/ndsj?themeCode=sjndsjFbk&folderId=03defd279a3c485f927a9e191aa9a4a4'
    #driver.get(url1)
    #time.sleep(4)

    js = 'window.open("https://data.tjj.cq.gov.cn/cqtjjfbk/#/publishingIndicatorSearch/ndsj?themeCode=sjndsjFbk&folderId=03defd279a3c485f927a9e191aa9a4a4");'
    driver.execute_script(js)
    time.sleep(3)

    # # 找出新窗口：
    new_window = driver.window_handles[-1]  # '-1'代表打开的最后一个窗口
    # 切换到新窗口：
    driver.switch_to.window(new_window)
    # driver.get(url1)
    print('----open-new-web----')
    time.sleep(3)
    page_text2 = driver.page_source

    tree = etree.HTML(page_text2)
    data2 = tree.xpath('//tr[@class="el-table__row"]')
    data2 = data2[0].xpath('./td')
    #data2 = tree.xpath('//tr[@class="el-table__row"]/td')
    title2 = data2[0].xpath('./div/span/text()')[0]
    print(title2)
    eco_everybody = []
    #### 2、人均可支配收入------------------------------------------------------------------------------------------------
    for info in data2[2:10]:
        num = info.xpath('./div/div/span/text()')[0]
        print(num)
        eco_everybody.append(num)
    # 关闭当前窗口，转回句柄到第一个窗口
    driver.close()
    driver.switch_to.window(driver.window_handles[0])




    # 3、cpi，居民消费指数
    url2 = 'https://data.tjj.cq.gov.cn/cqtjjfbk/#/publishingIndicatorSearch/ndsj?themeCode=sjndsjFbk&folderId=451fb050ce134d4ebfba80ec8e865b7c'

    js = 'window.open("https://data.tjj.cq.gov.cn/cqtjjfbk/#/publishingIndicatorSearch/ndsj?themeCode=sjndsjFbk&folderId=451fb050ce134d4ebfba80ec8e865b7c");'
    driver.execute_script(js)
    time.sleep(3)

    # # 找出新窗口：
    new_window = driver.window_handles[-1]  # '-1'代表打开的最后一个窗口
    # 切换到新窗口：
    driver.switch_to.window(new_window)
    # driver.get(url1)
    print('----open-new-web----')
    time.sleep(3)
    page_text2 = driver.page_source

    tree = etree.HTML(page_text2)
    data2 = tree.xpath('.//tr[@class="el-table__row"]')
    data2 = data2[0].xpath('./td')


    #data2 = tree.xpath('//tr[@class="el-table__row"]/td')
    title2 = data2[0].xpath('./div/span/text()')[0]
    print(title2)
    eco_cpi = []
    #### 3、cpi  居民消费价格指数------------------------------------------------------------------------------------------
    for info in data2[1:12]:
        num = info.xpath('./div/div/span/text()')[0]
        print(num)
        eco_cpi.append(num)

    df = pd.concat([pd.DataFrame({'gdp': eco_gdp}), pd.DataFrame({'per_income': eco_add_fact}),pd.DataFrame({'cpi': eco_everybody}), pd.DataFrame({'eva': eco_cpi})], axis=1)
    df = df.fillna(0)
    print('第一张表')
    print(df)


    # 关闭当前窗口，转回句柄到第一个窗口
    driver.close()
    driver.switch_to.window(driver.window_handles[0])
    print('******   part1 is finish   ********')

    ##########   part2.生态建设   #########
    # 1、森林覆盖率
    url2 = 'https://data.tjj.cq.gov.cn/cqtjjfbk/#/publishingIndicatorSearch/ndsj?themeCode=sjndsjFbk&folderId=cd1da7a9e0fa493b8137c8e2652b3a0b'
    js = 'window.open("https://data.tjj.cq.gov.cn/cqtjjfbk/#/publishingIndicatorSearch/ndsj?themeCode=sjndsjFbk&folderId=cd1da7a9e0fa493b8137c8e2652b3a0b");'
    driver.execute_script(js)
    time.sleep(3)
    # # 找出新窗口：
    new_window = driver.window_handles[-1]  # '-1'代表打开的最后一个窗口
    # 切换到新窗口：
    driver.switch_to.window(new_window)
    # driver.get(url1)
    print('----open-new-web----')
    time.sleep(3)
    page_text2 = driver.page_source

    tree = etree.HTML(page_text2)
    data2 = tree.xpath('//tr[@class="el-table__row"]')
    #### 1、森林覆盖率
    data2 = data2[6].xpath('./td')
    # data2 = tree.xpath('//tr[@class="el-table__row"]/td')
    title2 = data2[0].xpath('./div/span/text()')[0]
    print(title2)
    green_cover = []
    #### 1、森林覆盖率----------------------------------------------------------------------------------------------------
    for info in data2[1:10]:
        num = info.xpath('./div/div/span/text()')[0]
        print(num)
        green_cover.append(num)
    #### 2、森林面积
    data3 = tree.xpath('//tr[@class="el-table__row"]')
    data3 = data3[4].xpath('./td')
    title3 = data3[0].xpath('./div/span/text()')[0]
    print(title3)

    green_area=[]
    #### 2、森林面积------------------------------------------------------------------------------------------------------
    for info in data3[4:14]:
        num = info.xpath('./div/div/span/text()')[0]
        print(num)
        green_area.append(num)

    # #### 3、水资源总量
    # data3 = tree.xpath('//tr[@class="el-table__row"]')
    # data3 = data3[8].xpath('./td')
    # title3 = data3[0].xpath('./div/span/text()')[0]
    # print(title3)
    #
    # #### 3、水资源总量
    # for info in data3[1:6]:
    #     num = info.xpath('./div/div/span/text()')[0]
    #     print(num)

    # 关闭当前窗口，转回句柄到第一个窗口
    driver.close()
    driver.switch_to.window(driver.window_handles[0])

    # 3、水资源总量
    url2 = 'https://data.tjj.cq.gov.cn/cqtjjfbk/#/publishingIndicatorSearch/ndsj?themeCode=sjndsjFbk&folderId=8bfb7c20e779474aa3ed4c3f52f42166'
    js = 'window.open("https://data.tjj.cq.gov.cn/cqtjjfbk/#/publishingIndicatorSearch/ndsj?themeCode=sjndsjFbk&folderId=8bfb7c20e779474aa3ed4c3f52f42166");'
    driver.execute_script(js)
    time.sleep(3)
    # # 找出新窗口：
    new_window = driver.window_handles[-1]  # '-1'代表打开的最后一个窗口
    # 切换到新窗口：
    driver.switch_to.window(new_window)
    # driver.get(url1)
    print('----open-new-web----')
    time.sleep(3)
    page_text3 = driver.page_source

    tree = etree.HTML(page_text3)
    data2 = tree.xpath('//tr[@class="el-table__row"]')
    #### 3、水资源总量
    data2 = data2[1].xpath('./td')
    # data2 = tree.xpath('//tr[@class="el-table__row"]/td')
    title2 = data2[0].xpath('./div/span/text()')[0]
    print(title2)
    green_water = []
    #### 3、水资源总量----------------------------------------------------------------------------------------------------
    for info in data2[1:10]:
        num = info.xpath('./div/div/span/text()')[0]
        print(num)
        green_water.append(num)


    # 4、生活污水排放量
    #tree = etree.HTML(page_text3)
    data2 = tree.xpath('//tr[@class="el-table__row"]')
    data2 = data2[3].xpath('./td')
    # data2 = tree.xpath('//tr[@class="el-table__row"]/td')
    title2 = data2[0].xpath('./div/span/text()')[0]
    print(title2)
    green_dirty_water=[]
    #### 4、生活污水排放量-------------------------------------------------------------------------------------------------
    for info in data2[1:10]:
        num = info.xpath('./div/div/span/text()')[0]
        print(num)
        green_dirty_water.append(num)

    df2 = pd.concat([pd.DataFrame({'forest_cover': green_cover}), pd.DataFrame({'forest_area': green_area}),
                    pd.DataFrame({'water_resource': green_water}), pd.DataFrame({'dirty_water': green_dirty_water})], axis=1)
    df2 = df2.fillna(0)
    print('第二张表')
    print(df2)

    print('******   part2 is finish   ********')


    ##############     part3.碳排放量化     ################
    tree = etree.HTML(page_text2)
    data2 = tree.xpath('//tr[@class="el-table__row"]')



    #### 1、原煤总量
    data2 = data2[10].xpath('./td')
    # data2 = tree.xpath('//tr[@class="el-table__row"]/td')
    title2 = data2[0].xpath('./div/span/text()')[0]
    print(title2)
    mei_total = []
    #### 1、原煤总量------------------------------------------------------------------------------------------------------
    for info in data2[1:10]:
        num = info.xpath('./div/div/span/text()')[0]
        print(num)
        mei_total.append(num)
    #### 2、天然气总量
    data2 = tree.xpath('//tr[@class="el-table__row"]')
    data2 = data2[-3].xpath('./td')
    # data2 = tree.xpath('//tr[@class="el-table__row"]/td')
    title2 = data2[0].xpath('./div/span/text()')[0]
    print(title2)
    air_total =[]
    #### 2、天然气总量----------------------------------------------------------------------------------------------------
    for info in data2[3:11]:
        num = info.xpath('./div/div/span/text()')[0]
        print(num)
        air_total.append(num)
    # 关闭当前窗口，转回句柄到第一个窗口
    driver.close()
    driver.switch_to.window(driver.window_handles[0])


    #### 3、石油用量
    url3= 'https://data.tjj.cq.gov.cn/cqtjjfbk/#/publishingIndicatorSearch/ndsj?themeCode=sjndsjFbk&folderId=aa34ae03ba454a42b4186d5ae9d167eb'
    js = 'window.open("https://data.tjj.cq.gov.cn/cqtjjfbk/#/publishingIndicatorSearch/ndsj?themeCode=sjndsjFbk&folderId=aa34ae03ba454a42b4186d5ae9d167eb");'
    driver.execute_script(js)
    time.sleep(3)
    # # 找出新窗口：
    new_window = driver.window_handles[-1]  # '-1'代表打开的最后一个窗口
    # 切换到新窗口：
    driver.switch_to.window(new_window)
    # driver.get(url1)
    print('----open-new-web----')
    time.sleep(3)
    page_text = driver.page_source

    tree = etree.HTML(page_text)
    data2 = tree.xpath('//tr[@class="el-table__row"]')
    #### 3、石油用量
    data2 = data2[3].xpath('./td')
    # data2 = tree.xpath('//tr[@class="el-table__row"]/td')
    title2 = data2[0].xpath('./div/span/text()')[0]
    print(title2)
    oil_total_use=[]
    #### 3、石油用量------------------------------------------------------------------------------------------------------
    for info in data2[1:11]:
        num = info.xpath('./div/div/span/text()')[0]
        print(num)
        oil_total_use.append(num)
    # 关闭当前窗口，转回句柄到第一个窗口
    driver.close()
    driver.switch_to.window(driver.window_handles[0])



    ### 4、工业废气排放总量
    url4= 'https://data.tjj.cq.gov.cn/cqtjjfbk/#/publishingIndicatorSearch/ndsj?themeCode=sjndsjFbk&folderId=77b66c6c04d24fb397cbe06de64f1fb0'
    js = 'window.open("https://data.tjj.cq.gov.cn/cqtjjfbk/#/publishingIndicatorSearch/ndsj?themeCode=sjndsjFbk&folderId=77b66c6c04d24fb397cbe06de64f1fb0");'
    driver.execute_script(js)
    time.sleep(3)
    # # 找出新窗口：
    new_window = driver.window_handles[-1]  # '-1'代表打开的最后一个窗口
    # 切换到新窗口：
    driver.switch_to.window(new_window)
    # driver.get(url1)
    print('----open-new-web----')
    time.sleep(3)
    page_text4 = driver.page_source

    tree = etree.HTML(page_text4)
    data2 = tree.xpath('//tr[@class="el-table__row"]')
    #### 4、二氧化碳排放值由excel引入
    data2 = data2[0].xpath('./td')
    # data2 = tree.xpath('//tr[@class="el-table__row"]/td')
    title2 = data2[0].xpath('./div/span/text()')[0]
    print(title2)
    dirty_total_air = []
    #### 4、工业废气排放总量-----------------------------------------------------------------------------------------------
    for info in data2[1:11]:
        num = info.xpath('./div/div/span/text()')[0]
        print(num)
        dirty_total_air.append(num)
    # 关闭当前窗口，转回句柄到第一个窗口
    driver.close()
    driver.switch_to.window(driver.window_handles[0])

    df3 = pd.concat([pd.DataFrame({'raw_coal': mei_total}), pd.DataFrame({'natural_gas': air_total}),
                     pd.DataFrame({'oil': oil_total_use}), pd.DataFrame({'carbon_dioxide': green_dirty_water})],
                    axis=1)
    df3 = df3.fillna(0)
    print('第三张表')
    print(df3)

    print('******   part3 is finish   ********')


    #########         part4.能源结构      ##############
    #### 1、能源消耗总量
    url5 = 'https://data.tjj.cq.gov.cn/cqtjjfbk/#/publishingIndicatorSearch/ndsj?themeCode=sjndsjFbk&folderId=aa34ae03ba454a42b4186d5ae9d167eb'
    js = 'window.open("https://data.tjj.cq.gov.cn/cqtjjfbk/#/publishingIndicatorSearch/ndsj?themeCode=sjndsjFbk&folderId=aa34ae03ba454a42b4186d5ae9d167eb");'
    driver.execute_script(js)
    time.sleep(3)
    # # 找出新窗口：
    new_window = driver.window_handles[-1]  # '-1'代表打开的最后一个窗口
    # 切换到新窗口：
    driver.switch_to.window(new_window)
    print('----open-new-web----')
    time.sleep(3)
    page_text = driver.page_source

    tree = etree.HTML(page_text)
    data2 = tree.xpath('//tr[@class="el-table__row"]')
    #### 1、能源消耗总量
    data2 = data2[0].xpath('./td')
    # data2 = tree.xpath('//tr[@class="el-table__row"]/td')
    title2 = data2[0].xpath('./div/span/text()')[0]
    print(title2)
    power_cost=[]
    #### 1、能源消耗总量---------------------------------------------------------------------------------------------------
    for info in data2[1:11]:
        num = info.xpath('./div/div/span/text()')[0]
        print(num)
        power_cost.append(num)

    #### 2、煤炭消费总量
    data2 = tree.xpath('//tr[@class="el-table__row"]')
    #### 2、煤炭消费总量
    data2 = data2[1].xpath('./td')
    # data2 = tree.xpath('//tr[@class="el-table__row"]/td')
    title2 = data2[0].xpath('./div/span/text()')[0]
    print(title2)
    mei_cost =[]
    #### 2、煤炭消费总量---------------------------------------------------------------------------------------------------
    for info in data2[1:11]:
        num = info.xpath('./div/div/span/text()')[0]
        print(num)
        mei_cost.append(num)

    #### 3、天然气消费总量
    data2 = tree.xpath('//tr[@class="el-table__row"]')
    #### 3、天然气消费总量
    data2 = data2[2].xpath('./td')
    # data2 = tree.xpath('//tr[@class="el-table__row"]/td')
    title2 = data2[0].xpath('./div/span/text()')[0]
    print(title2)
    air_cost=[]
    #### 3、天然气消费总量-------------------------------------------------------------------------------------------------
    for info in data2[1:11]:
        num = info.xpath('./div/div/span/text()')[0]
        print(num)
        air_cost.append(num)

    #### 4、油料消费总量
    data2 = tree.xpath('//tr[@class="el-table__row"]')
    #### 4、油料消费总量
    data2 = data2[3].xpath('./td')
    # data2 = tree.xpath('//tr[@class="el-table__row"]/td')
    title2 = data2[0].xpath('./div/span/text()')[0]
    print(title2)
    oil_cost = []
    #### 4、油料消费总量---------------------------------------------------------------------------------------------------
    for info in data2[1:11]:
        num = info.xpath('./div/div/span/text()')[0]
        print(num)
        oil_cost.append(num)

    #### 5、电力消费总量
    data2 = tree.xpath('//tr[@class="el-table__row"]')
    #### 5、电力消费总量
    data2 = data2[4].xpath('./td')
    # data2 = tree.xpath('//tr[@class="el-table__row"]/td')
    title2 = data2[0].xpath('./div/span/text()')[0]
    print(title2)
    ele_cost=[]
    #### 5、电力消费总量---------------------------------------------------------------------------------------------------
    for info in data2[1:11]:
        num = info.xpath('./div/div/span/text()')[0]
        print(num)
        ele_cost.append(num)

    df4 = pd.concat([pd.DataFrame({'energy_c': power_cost}), pd.DataFrame({'coal_c': mei_cost}),
                     pd.DataFrame({'gas_c': air_cost}), pd.DataFrame({'oil_c': oil_cost}),pd.DataFrame({'electricity_c': ele_cost})],
                    axis=1)
    df4 = df4.fillna(0)
    print('第三张表')
    print(df4)



    # 关闭当前窗口，转回句柄到第一个窗口
    driver.close()
    driver.switch_to.window(driver.window_handles[0])

    print('******   part4 is finish   ********')
    print('yes !!!')


    #####       全部完成，退出程序      ######
    #### 全部完成，关闭浏览器....
    print('*********** 全部完成 ***********')
    driver.quit()

    data = [df,df2,df3,df4]

    return data





